package persistencia;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.AdditionalLine;
import model.Service;
import model.User;


public class ServiceMapper {

	private static ServiceMapper s_serviceMapper;

	private ServiceMapper() {
	}

	public static ServiceMapper GetInstancia() {
		if (s_serviceMapper == null) {
			s_serviceMapper = new ServiceMapper();
		}

		return s_serviceMapper;
	}
	
	public void InsertService(Service s)
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();

		try {
			String senten = "INSERT INTO service (Name, serviceUnit) VALUES (?,?)";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, s.getServiceName());
			ps.setString(2, s.getServiceUnit());
			
			s.setServiceId( ps.executeUpdate());

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Message Error during Service Insertion: "
					+ e.getMessage());
			System.out.println("Stack Trace Error for  Service Insertion Error:  "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}

		
	}
	
	public void UpdateService(Service s)
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();

		try {
			String senten = "update service set Name = ?, serviceUnit = ? where serviceId =?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, s.getServiceName());
			ps.setString(2, s.getServiceUnit());
			ps.setInt(3, s.getServiceId());
			
			ps.executeUpdate();

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Message Error during Service Update: "
					+ e.getMessage());
			System.out.println("Stack Trace Error for  Service Update Error:  "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
	}
	
	
	public void DeleteService(Service s)
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();

		try {
			String senten = "delete from service where name = ?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, s.getServiceName());
			
			ps.executeUpdate();

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Message Error during Service Deletion: "
					+ e.getMessage());
			System.out.println("Stack Trace Error for  Service Deletion Error:  "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		
	}
	
	public Service BuscarService(String s)
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();
		Service service = null;
		try {
			String senten = "select name, serviceUnit from service where name = ?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, s);
			
			ResultSet result = ps.executeQuery();
			if (result.next())
			{
				service = new Service();
				service.populateService(result.getString("name"),
							result.getString("serviceUnit"));
			}

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Message Error during Service Search: "
					+ e.getMessage());
			System.out.println("Stack Trace Error for  Service Search Error:  "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return service;
	}
	
	
	public ArrayList<Service> LoadAllServices()
	{
		ArrayList<Service> ret = new ArrayList<Service>();
		Connection con = PoolConnection.getPoolConnection().getConnection();
		Service service = null;
		try {
			String senten = "select  name, serviceUnit from service";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			
			ResultSet result = ps.executeQuery();
			while (result.next())
			{
				service = new Service();
				service.populateService(result.getString("name"),result.getString("serviceUnit"));
				ret.add(service);
			}

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Message Error during Services Load:: "
					+ e.getMessage());
			System.out.println("Stack Trace for Services Load Error: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return ret;
	}
}
